import pandas as pd
import json
import requests
from configs import *
# import pymysql
from loguru import logger


def get_keyword_from_LLM(content):
    # Define the URL and payload (body) data
    # url = "http://10.8.113.89:7666/job"
    url = LLM_URL
    payload = {
        "type": "从工单文本提取事件",
        "content": content,
        "format": False
    }

    # Send an HTTP POST request with JSON data
    try:
        response = requests.post(url, json=payload)

        # Check if the request was successful (status code 200)
        if response.status_code == 200:
            # Parse the JSON response
            response_data = response.json()

            # Now you can work with the response_data as a Python dictionary
            return response_data['response']  # Pretty print the JSON response
        else:
            print(f"Request failed with status code: {response.status_code}")
    except Exception as e:
        print(f"Error: {str(e)}")


def read_txt_file(lines, filename):
    texts = [i.strip() for i in lines if i.strip()]
    metadatas = [{"source":filename} for _ in range(len(texts))]
    return texts, metadatas


def read_excel_file(file, filename):
    df = pd.read_excel(file)
    # texts = df["Summary（工单内容）"].tolist()
    # ids = df["Wpid（工单编号）"].tolist()
    # texts = df["内容描述"].tolist()
    texts = df["工单内容"].tolist()
    ids = df["工单编号"].tolist()
    metadatas = [{"id":ids[i], 
        "source":filename,
        "id":ids[i],
        "contnet":texts[i]
        } for i in range(len(texts))]
    return texts, metadatas


def keyword_read_excel_file(file, filename):
    df = pd.read_excel(file)
    texts = df["Content"].tolist()
    if "算法1" in df.columns:
        keywords = df["算法1"].tolist()
    else:
        keywords = [get_keyword_from_LLM(i) for i in texts]
    metadatas = [{"source":filename,
                  "content": texts[i],
                  } for i in range(len(texts))]
    return keywords, metadatas


def read_json_file(file, filename):
    df = pd.DataFrame(file)
    texts = df["Summary（工单内容）"].tolist()
    ids = df["Wpid（工单编号）"].tolist()
    times = df["时间"].tolist()
    metadatas = [{"id":ids[i], 
                  "source":filename,
                  "time":times[i]} for i in range(len(texts))]
    return texts, metadatas


def keyword_read_json_file(file, filename):
    df = pd.DataFrame(file)
    texts = df["Summary（工单内容）"].tolist()
    keywords = [get_keyword_from_LLM(i) for i in texts]
    metadatas = [{"source":filename,
                  "content": texts[i],
                  }for i in range(len(texts))]
    return keywords, metadatas

def read_MySQL_DB(min_time = None, max_time = None):
    try:
        db_connect_admin = pymysql.connect(host=MYSQL_DICT["host"], port=MYSQL_DICT["port"], 
                                        user=MYSQL_DICT["user"], password=MYSQL_DICT["password"],
                                        database=MYSQL_DICT["database"]) 
                                        #autocommit=MYSQL_DICT["autocommit"], 
                                        #charset=MYSQL_DICT["charset"], connect_timeout=MYSQL_DICT["connect_timeout"])
    except:
        raise Exception("can't access hte db_connect_admin")
    try:
        id_arg = "工单编号"
        content_arg = "内容描述"
        time_arg = "工单生成时间"
        
        if (min_time is None) and (max_time is None):
            sql = f"""SELECT {id_arg}, {content_arg}, {time_arg} FROM {DB_NAME}"""
        if (min_time is None) and (max_time is not None):
            sql = f"""SELECT {id_arg}, {content_arg}, {time_arg} FROM {DB_NAME} WHERE {time_arg}<'{max_time}'"""
        if (min_time is not None) and (max_time is None):
            sql = f"""SELECT {id_arg}, {content_arg}, {time_arg} FROM {DB_NAME} WHERE {time_arg}>'{min_time}'"""
        if (min_time is not None) and (max_time is not None):
            sql = f"""SELECT {id_arg}, {content_arg}, {time_arg} FROM {DB_NAME} WHERE {time_arg}>'{min_time}' AND {time_arg}<'{max_time}'"""
        
        # sql += " LIMIT 450000"
        cursor = db_connect_admin.cursor()
        cursor.execute(sql)
        cursor_data = cursor.fetchall()
        

    # except Exception as e:
    #     raise Exception(f"{e}")
    # try:
        texts = [i[1] if i[1] is not None else "" for i in cursor_data]
        ids = [i[0] if i[0] is not None else -1 for i in cursor_data]
        metadatas = [{"content":texts[i],
                      "id":ids[i]} for i in range(len(texts))]
        times = [i[2] if i[2] is not None else None for i in cursor_data]
        
        # for i in cursor_data:
        #     data_list.append({"ids":i[0],"texts":i[1]})
        try:
            max_times = max(times)
        except:
            max_times = None 
        
    except:
        raise Exception("can't access to data")
    finally:
        cursor.close()
        db_connect_admin.close()
        if len(texts)>0:
            print("texts[0]: ", texts[0])
            print("metadatas[0]: ", metadatas[0])
        else:
            print("texts[0]: ", texts)
            print("metadatas[0]: ", metadatas)
        return texts, metadatas, max_times

    

